oracle销售开票数据流,ORACLE EBS AP发票到付款的数据流

--

1.发票创建时生成数据如下表

--

发票主表

SELECT

*

FROM AP_INVOICES_ALL A

WHERE A.INVOICE_NUM

=

'

20111213001

';

--

发票分配表

SELECT

*

FROM AP_INVOICE_DISTRIBUTIONS_ALL B

WHERE B.INVOICE_ID

=

697444;

--

发票付款计划表

SELECT

*

FROM AP_PAYMENT_SCHEDULES_ALL C

WHERE C.INVOICE_ID

=

697444;

--

From ap_invoices_all.invoice_id

--

2.发票验证并创建会计科目时产生的数据如下表

--

发票验证时产生的数据

SELECT

*

FROM AP_ACCOUNTING_EVENTS_ALL D

WHERE D.SOURCE_ID

=

697444

AND D.SOURCE_TABLE

=

'

AP_INVOICES

';

--

From ap_invoices_all.invoice_id

--

发票创建会计科目时产生的分录

SELECT

*

FROM AP_AE_HEADERS_ALL E

WHERE E.ACCOUNTING_EVENT_ID

=

1093101;

--

From AP_ACCOUNTING_EVENTS_ALL.souce_id = invoice_id

SELECT

*

FROM AP_AE_LINES_ALL G

WHERE G.AE_HEADER_ID

=

1088969;

--

3.发票分录查询(SOURCE_TABLE栏位分别是AP_INVOICE_DISTRIBUTIONS和AP_INVOICES，代表发票的分录和发票分配的分录)

SELECT AAL.SOURCE_TABLE

,AAL.

*

FROM AP_INVOICES_ALL AIA

,AP_ACCOUNTING_EVENTS_ALL AAE

,AP_AE_HEADERS_ALL AAH

,AP_AE_LINES_ALL AAL

WHERE AIA.INVOICE_NUM

=

'

20111213001

'

AND AIA.INVOICE_ID

= AAE.SOURCE_ID

AND AAE.SOURCE_TABLE

=

'

AP_INVOICES

'

AND AAH.ACCOUNTING_EVENT_ID

= AAE.ACCOUNTING_EVENT_ID

AND AAH.AE_HEADER_ID

= AAL.AE_HEADER_ID;

--

4.发票付款时产生的数据如下表

SELECT

*

FROM AP_INVOICE_PAYMENTS_ALL F

WHERE F.INVOICE_ID

=

697444;

--

CHECK_ID From AP_INVOICE_PAYMENTS_ALL

SELECT

*

FROM AP_CHECKS_ALL H

WHERE H.CHECK_ID

=

2357756;

--

CHECK_ID From AP_INVOICE_PAYMENTS_ALL.CHECK_ID

--

ACCOUNTING_EVENT_ID From AP_INVOICE_PAYMENTS_ALL.ACCOUNTING_EVENT_ID

SELECT

*

FROM AP_ACCOUNTING_EVENTS_ALL D

WHERE D.ACCOUNTING_EVENT_ID

=

1086193;

--

ACCOUNTING_EVENT_ID From AP_INVOICE_PAYMENTS_ALL.ACCOUNTING_EVENT_ID

--

5.付款分录查询

SELECT AAL.

*

FROM AP_CHECKS_ALL ACA

,AP_ACCOUNTING_EVENTS_ALL AAE

--

,AP_INVOICE_PAYMENTS_ALL AIP

,AP_AE_HEADERS_ALL AAH

,AP_AE_LINES_ALL AAL

WHERE ACA.CHECK_ID

= AAE.SOURCE_ID

AND AAE.SOURCE_TABLE

=

'

AP_CHECKS

'

AND AAE.ACCOUNTING_EVENT_ID

= AAH.ACCOUNTING_EVENT_ID

AND AAH.AE_HEADER_ID

= AAL.AE_HEADER_ID

--

AND AAL.SOURCE_ID = AIP.INVOICE_PAYMENT_ID--负债科目

--

AND AAL.SOURCE_ID = ACA.CHECK_ID--现金科目

AND AAH.ACCOUNTING_DATE

>= TO_DATE(

'

20110907

'

,

'

yyyymmdd

')

AND AAH.ACCOUNTING_DATE

< TO_DATE(

'

20110908

'

,

'

yyyymmdd

')

AND AAH.GL_TRANSFER_FLAG

=

'

Y

'

AND AAH.ORG_ID

=

236;

--

6. 发票核销预付款发票的金额

SELECT AID1.ROWID ROW_ID

,AID1.INVOICE_ID INVOICE_ID

,AID1.INVOICE_DISTRIBUTION_ID INVOICE_DISTRIBUTION_ID

,AID1.PREPAY_DISTRIBUTION_ID PREPAY_DISTRIBUTION_ID

,AID1.DISTRIBUTION_LINE_NUMBER PREPAY_DIST_NUMBER

,(

-

1)

* AID1.AMOUNT PREPAY_AMOUNT_APPLIED

,NVL(AID2.PREPAY_AMOUNT_REMAINING

,AID2.AMOUNT) PREPAY_AMOUNT_REMAINING

,AID1.DIST_CODE_COMBINATION_ID DIST_CODE_COMBINATION_ID

,AID1.ACCOUNTING_DATE ACCOUNTING_DATE

,AID1.PERIOD_NAME PERIOD_NAME

,AID1.SET_OF_BOOKS_ID SET_OF_BOOKS_ID

,AID1.DESCRIPTION DESCRIPTION

,AID1.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID

,AID1.RCV_TRANSACTION_ID RCV_TRANSACTION_ID

,AID1.ORG_ID ORG_ID

,AI.INVOICE_NUM PREPAY_NUMBER

,AI.VENDOR_ID VENDOR_ID

,AI.VENDOR_SITE_ID VENDOR_SITE_ID

,ATC.TAX_ID TAX_ID

,ATC.NAME TAX_CODE

FROM AP_INVOICES_ALL AI

,AP_INVOICE_DISTRIBUTIONS_ALL AID1

,AP_INVOICE_DISTRIBUTIONS_ALL AID2

,AP_TAX_CODES ATC

WHERE AID1.PREPAY_DISTRIBUTION_ID

= AID2.INVOICE_DISTRIBUTION_ID

AND AI.INVOICE_ID

= AID2.INVOICE_ID

AND AID1.AMOUNT